#include "connectdb.h"
#include<stdlib.h>
#include<string.h>
#include"book.h"
#include"manage.h"
#include<stdio.h>
using namespace std;

extern ManageBook Man_book;
extern ManageUser Man_user;
extern ManagePurchaseR Man_PurchaseR;
db::db():m_conn(NULL),m_res(NULL),m_row(NULL){}
bool db::db_init()
{
    m_conn = mysql_init(NULL); //初始化句柄
	if(m_conn == NULL)
	{
		cout<<"mysql_init error!!"<<endl;
		return false;
	}
    return true;
}
bool db::db_conn()
{
    const char *host = "localhost";  //主机
	const char *user = "root";       //用户名
	const char *pwd = "qq1";      //用户密码
	const char *db = "project"; //数据库名称
	m_conn = mysql_real_connect(m_conn,host,user,pwd,db,0,NULL,0);  //连接
	mysql_set_character_set(m_conn,"utf8");
	if(m_conn == NULL)
	{
		cout<<mysql_error(m_conn)<<endl;
		return false;
	}
    const char *q = "use project";
    mysql_real_query(m_conn, q, strlen(q));
	q = "create table if not exists book(no int primary key not null,name char(40),autor char(20),press char(50),price double(7,3),number int)default charset = utf8";
    mysql_real_query(m_conn,q,strlen(q));
    q = "create table if not exists purchaseRecord(no int primary key not null,nameOfUser char(40),nameOfBook char(40),purchaseTime datetime,cost double(7,3),advise varchar(100))default charset = utf8";
    mysql_real_query(m_conn,q,strlen(q));
    q= "create table if not exists user(ID int primary key not null,name char(40),password char(20),level int,phone char(20),address char(50),cost double(7,3),LoginTime datetime,balance double(7,3))default charset = utf8";
    mysql_real_query(m_conn,q,strlen(q));
    return true;

}
bool db::dbToList()
{
	const char *q = "select * from book";
	mysql_real_query(m_conn,q,strlen(q));
	m_res = mysql_use_result(m_conn);
	if(m_res == NULL)
	{
		cout<<"mysql_use_result error!!"<<endl;
		return false;
	}
	while( (m_row = mysql_fetch_row(m_res)) != NULL) 
	{
		book temp(m_row[1],m_row[2],m_row[3],atof(m_row[4]),atoi(m_row[5]));
		Man_book.addBook(temp);
	}

	q = "select * from user";
	mysql_real_query(m_conn,q,strlen(q));
	m_res = mysql_use_result(m_conn);
	if(m_res == NULL)
	{
		cout<<"mysql_use_result error!!"<<endl;
		return false;
	}
	while( (m_row = mysql_fetch_row(m_res)) != NULL) 
	{
		string passwd;
		int len  = strlen(m_row[2]);
		for(int i = 1; i < len -1; ++i)
			passwd[i] = m_row[2][i];
		cout<<passwd<<endl;
		user temp(atoi(m_row[0]),m_row[1],m_row[2],atoi(m_row[3]),m_row[4],m_row[5],atof(m_row[6]),m_row[7],atof(m_row[8]));
		Man_user.adduser(temp);
	}
	
	q = "select * from purchaseRecord";
	mysql_real_query(m_conn,q,strlen(q));
	m_res = mysql_use_result(m_conn);
	if(m_res == NULL)
	{
		cout<<"mysql_use_result error!!"<<endl;
		return false;
	}
	while( (m_row = mysql_fetch_row(m_res)) != NULL) 
	{
		purchaseRecord temp(m_row[1],m_row[2],m_row[3],atof(m_row[4]),m_row[5]);
		Man_PurchaseR.addPurchaseRecord(temp);
	}
	

    return true;
}
bool db::listToDb()
{
	const char *q = "delete from book";
	mysql_query(m_conn, q);
	char buff[1024] = {'\0'};
	char buf[100] = "insert into book values(";
	vector<book>::iterator i = ManageBook::bookList.begin();
	int no = 0;
	while(i != ManageBook::bookList.end())
	{
		no++;
		sprintf(buff,"%s %d,'%s','%s','%s',%f,%d);",buf,
				no,
				((*i).m_name).data(),
				((*i).m_autor).data(),
				((*i).m_press).data(),
				(*i).m_price,
				(*i).m_number
				
			   );
		mysql_query(m_conn,buff);
		i++;
	}
	//user table save
	q = "delete from user";
	mysql_query(m_conn, q);
	char buffUser[1024] = {'\0'};
	char bufUser[100] = "insert into user values(";
	vector<user>::iterator it = ManageUser::userList.begin();
	while(it != ManageUser::userList.end())
	{
		

		sprintf(buffUser,"%s %d,'%s','%s',%d,'%s','%s',%f,'%s',%f);",bufUser,
				(*it).m_ID,
				((*it).m_name).data(),
				((*it).m_passwd).data(),
				(*it).m_level,
				((*it).m_phone).data(),
				((*it).m_address).data(),
				(*it).m_cost,
				((*it).m_LoginTime).data(),
				(*it).m_balance
			   );
		mysql_query(m_conn,buffUser);
		it++;
	}

	//购买记录的信息的保存
	q = "delete from purchaseRecord";
	mysql_query(m_conn, q);
	char buffp[100] = {'\0'};
	char bufp[100] = "insert into purchaseRecord values(";
	vector<purchaseRecord>::iterator itp = ManagePurchaseR::purchaseRecordList.begin();
	no = 0;
	while(itp != ManagePurchaseR::purchaseRecordList.end())
	{
		no ++;
		sprintf(buffp,"%s %d,'%s','%s','%s',%f,'%s');",bufp,
				no,
				((*itp).m_nameOfuser).data(),
				((*itp).m_nameOfBook).data(),
				((*itp).m_purchaseTime).data(),
				((*itp).m_cost),
				((*itp).m_advise).data()
			   );
		mysql_query(m_conn,buffp);
		cout<<bufp<<endl;
		itp++;
	}
    return true;
}
bool db::free_close_conn()
{
    mysql_free_result(m_res);
	mysql_close(m_conn);
    return true;
}
